package cn.edu.scau.cmi.lijiongtao.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.Button;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.Label;
import javafx.scene.layout.GridPane;

public class Student extends GridPane {
	// 声明连接对象
	private Connection connection = null;
	// 驱动程序名
	private String driver = "com.mysql.jdbc.Driver";
	// URL
	private String url = "jdbc:mysql://localhost:3306/program6";
	// User
	private String user = "root";
	private String password = "ljt123456";
	// teacher
	private String tName = "";
	private String tID = "";
	// 登陆人信息
	private String olName = "";
	private String olId = "";

	public Student(String id, String name) {
		this.olId = id;
		this.olName = name;
		init();
	}

	private void init() {
		Label lbStu = new Label("学生：");
		Label txtStu = new Label(this.olName);
		Label lbTea = new Label("指导老师：");
		ChoiceBox<String> cb = new ChoiceBox<String>();
		ObservableList<String> items = FXCollections.observableArrayList();
		/*
		 * cb.getSelectionModel().selectedIndexProperty().addListener((pv, old, newv) ->
		 * { this.tName = newv.toString(); });
		 */
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			boolean flag = false;
			if (!connection.isClosed()) {
				System.out.println("connect succeed");
				// 创建statement对象，用来执行SQL语句
				Statement statement = connection.createStatement();
				// SQL
				String sql1 = "select * from teach where s_id=" + this.olId;
				ResultSet rSet = statement.executeQuery(sql1);
				while (rSet.next()) {
					System.out.println("find");
					tID = rSet.getString("t_id");
				}
				String sql = "select * from teacher";
				ResultSet rs = statement.executeQuery(sql);
				String user = "";
				while (rs.next()) {
					user = rs.getString("name");
					if (this.tID.equals(rs.getString("id")))
						this.tName = user;
					items.add(user);
				}
				rs.close();
				connection.close();
			}
		} catch (ClassNotFoundException e2) {
			System.out.println("数据库驱动异常");
		} catch (SQLException e3) {
			e3.printStackTrace();
		}

		cb.setItems(items);
		cb.getSelectionModel().select(tName);

		Button button = new Button("选择/修改");

		this.addRow(2, lbStu, txtStu);
		this.addRow(4, lbTea, cb);
		this.addRow(6, button);

		button.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				boolean flag = false;
				if (!connection.isClosed()) {
					System.out.println("connect succeed");
					// 创建statement对象，用来执行SQL语句
					Statement statement = connection.createStatement();
					// SQL
					String sql = "";
					ResultSet rs;
					if (tID != "") {
						tName = cb.getSelectionModel().getSelectedItem();
						sql = "select * from teacher where name = '" + tName + "';";
						rs = statement.executeQuery(sql);
						while (rs.next()) {
							tID = rs.getString("id");
						}
						rs.close();

						System.out.println(sql);
						sql = "update teach set t_id = " + tID + " where s_id = '" + olId + "';";

						System.out.println(sql);
						statement.executeUpdate(sql);
						Alert alert = new Alert(AlertType.INFORMATION);
						alert.setTitle("提示");
						alert.setHeaderText(null);
						alert.setContentText("修改成功！");
						alert.showAndWait();
					} else {
						tName = cb.getSelectionModel().getSelectedItem();
						sql = "select * from teacher where name = '" + tName + "';";
						rs = statement.executeQuery(sql);
						while (rs.next()) {
							tID = rs.getString("id");
						}
						rs.close();
						sql = "insert into teach(s_id,t_id) values(" + olId + "," + tID + ")";
						statement.executeUpdate(sql);
						Alert alert = new Alert(AlertType.INFORMATION);
						alert.setTitle("提示");
						alert.setHeaderText(null);
						alert.setContentText("选择成功！");
						alert.showAndWait();
					}
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
	}
}
